package com.onlineMIS.ORM.DAO.chainS.vip;

import java.sql.Date;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Restrictions;
import org.springframework.stereotype.Repository;

import com.onlineMIS.ORM.DAO.BaseDAO;
import com.onlineMIS.ORM.entity.chainS.vip.ChainVIPCard;
import com.onlineMIS.ORM.entity.chainS.vip.ChainVIPPrepaidFlow;
import com.onlineMIS.ORM.entity.chainS.vip.ChainVIPScore;
import com.onlineMIS.common.Common_util;

@Repository
public class ChainVIPScoreImpl extends BaseDAO<ChainVIPScore> {
	
		public double getVIPScoreSum(int vipCardId){
			Object[] values = new Object[]{vipCardId};
			String sql = "select sum(coupon) from ChainVIPScore where vipCardId = ?";
			List<Object> results = this.executeHQLSelect(sql, values,null, true);
			if (results == null || results.size() == 0)
				return 0;
			else 
				return Common_util.getDouble(results.get(0));
		}
		
		/**
		 * Map key : card number
		 * Map value: List<Accumulated value, consumed value>
		 * @param vipCardNos
		 * @return
		 */
		public Map<Integer, List<Double>> getVIPCardScore(List<ChainVIPCard> vipCardNos){
			Map<Integer, List<Double>> vipCardMap = new HashMap<Integer, List<Double>>();
			Map<Integer, Double> vipCardMap_accu = new HashMap<Integer, Double>();
			Map<Integer, Double> vipCardMap_consu = new HashMap<Integer, Double>();
			
			if (vipCardNos != null && vipCardNos.size() >0){
				String vipCardList = "(";
				for (ChainVIPCard vipCardNo : vipCardNos)
					vipCardList += "'" + vipCardNo.getId() + "',";
				vipCardList += "'" + vipCardNos.get(0).getId() + "')";
				
				String sql_accu = "select vipCardId, sum(coupon) from ChainVIPScore where vipCardId in "+ vipCardList +" and coupon > 0 group by vipCardId";
				String sql_consum = "select vipCardId, sum(coupon) from ChainVIPScore where vipCardId in "+ vipCardList +" and coupon < 0 group by vipCardId";
				
				List<Object> results_accu = this.executeHQLSelect(sql_accu, new Object[]{}, null,true);
				List<Object> results_consum = this.executeHQLSelect(sql_consum, new Object[]{},null, true);
				
				if (results_accu != null){
				    for (Object object : results_accu){
				    	Object[] oneRecord = (Object[])object;
				    	int vipCardId = (Integer)oneRecord[0];
				    	double coupons = (Double)oneRecord[1];
				    	vipCardMap_accu.put(vipCardId, coupons);
				    }
				} 
				if (results_consum != null){
				    for (Object object : results_consum){
				    	Object[] oneRecord = (Object[])object;
				    	Integer vipCardId = (Integer)oneRecord[0];
				    	double coupons = (Double)oneRecord[1];
				    	vipCardMap_consu.put(vipCardId, coupons);
				    }
				} 
				for (ChainVIPCard vipCard : vipCardNos){
					int vipCardId = vipCard.getId();
					Double accu = vipCardMap_accu.get(vipCardId);
					Double consu = vipCardMap_consu.get(vipCardId);
					List<Double> values = new ArrayList<Double>();
					if (accu != null)
					   values.add(accu);
					else 
						values.add(new Double(0));
					
					if (consu != null)
						values.add(Math.abs(consu));
					else 
					    values.add(new Double(0));					
					vipCardMap.put(vipCardId, values);
				}
				
				//System.out.println(vipCardMap);
			}
			
			return vipCardMap;
		}

		/**
		 * 获取vip卡的最后消费日期
		 * @param vipCardNos
		 * @return
		 */
		public Map<Integer, Date> getVIPCardLastConsump(
				List<ChainVIPCard> vipCardNos) {
			Map<Integer, Date> vipCardMap = new HashMap<Integer, Date>();

			if (vipCardNos != null && vipCardNos.size() >0){
				String vipCardList = "(";
				for (ChainVIPCard vipCardNo : vipCardNos)
					vipCardList += "'" + vipCardNo.getId() + "',";
				vipCardList += "'" + vipCardNos.get(0).getId() + "')";
				
				String sql = "select vipCardId, max(date) from ChainVIPScore where type !='"+ChainVIPScore.TYPE_MANUAL_ADJUST+"' AND vipCardId in "+ vipCardList +" group by vipCardId";
				
				List<Object> results_accu = this.executeHQLSelect(sql, new Object[]{}, null,true);
				
				if (results_accu != null){
				    for (Object object : results_accu){
				    	Object[] oneRecord = (Object[])object;
				    	int vipCardId = (Integer)oneRecord[0];
				    	Date consumpDate = (Date)oneRecord[1];
				    	vipCardMap.put(vipCardId, consumpDate);
				    }
				} 

			}
			
			return vipCardMap;
		}

		/**
		 * 如果是预存就要记录
		 * @param vipPrepaid
		 */
		public void saveCascadingVIPPrepaid(ChainVIPPrepaidFlow vipPrepaid) {
			ChainVIPScore vipScore = new ChainVIPScore();
			vipScore.setChainId(vipPrepaid.getChainStore().getChain_id());
			String additionalComment = "";
			if (vipPrepaid.getStatus() == ChainVIPPrepaidFlow.STATUS_CANCEL)
				additionalComment = "红冲";
			
			if (vipPrepaid.getDepositType().equalsIgnoreCase(ChainVIPPrepaidFlow.DEPOSIT_TYPE_CARD))
				additionalComment += "预存刷卡 ";
			else if (vipPrepaid.getDepositType().equalsIgnoreCase(ChainVIPPrepaidFlow.DEPOSIT_TYPE_CASH))
				additionalComment += "预存现金 ";
			else if (vipPrepaid.getDepositType().equalsIgnoreCase(ChainVIPPrepaidFlow.DEPOSIT_TYPE_ALIPAY))
				additionalComment += "预存支付宝 ";
			else if (vipPrepaid.getDepositType().equalsIgnoreCase(ChainVIPPrepaidFlow.DEPOSIT_TYPE_WECHAT))
				additionalComment += "预存微信 ";			
			vipScore.setComment(additionalComment + vipPrepaid.getComment());
			vipScore.setDate(vipPrepaid.getCreateDate());
			vipScore.setOrderId(vipPrepaid.getId());
			vipScore.setSalesValue(vipPrepaid.getAmount());
			vipScore.setVipCardId(vipPrepaid.getVipCard().getId());
			vipScore.setVipCardNo(vipPrepaid.getVipCard().getVipCardNo());
			vipScore.setType(ChainVIPScore.TYPE_DEPOSIT);
			save(vipScore, true);
			
		}
		
		
		/**
		 * VIP Score积分兑换次数，
		 * @param vipIdSet
		 * @param startDate
		 * @param endDate
		 * @return
		 */
		public Map<Integer, Integer> getVipScoreMap(String vipIdSet,java.util.Date startDate,java.util.Date endDate) {
			Map<Integer, Integer> vipScoreMap = new HashMap<Integer, Integer>();
			
			String hqlCountOfScoreUse = "SELECT cvp.vipCardId, COUNT(cvp.vipCardId) FROM ChainVIPScore cvp WHERE cvp.type=? AND cvp.coupon < 0 AND  cvp.date BETWEEN ? AND ? AND cvp.vipCardId IN "+ vipIdSet+" GROUP BY cvp.vipCardId";
			Object[] sqlValue2 = new Object[]{ChainVIPScore.TYPE_SALE, startDate, endDate};
			List<Object> vipScoreMapValue = (List<Object>)this.executeHQLSelect(hqlCountOfScoreUse, sqlValue2, null, false);
			for (Object object : vipScoreMapValue) {
				Object[] sales3 = (Object[])object;
				vipScoreMap.put(Common_util.getInt(sales3[0]), Common_util.getInt(sales3[1]));
			}
			
			return vipScoreMap;
		}
		
		
		/**
		 * VIP Score积分兑换现金总数
		 * @param vipIdSet
		 * @param startDate
		 * @param endDate
		 * @return
		 */
		public Map<Integer,Double> getVipExchangeMap(String vipIdSet,java.util.Date startDate,java.util.Date endDate){
			Map<Integer, Double> vipExchangeMap = new HashMap<Integer, Double>();
			String exChange = "SELECT cvp.vipCard.id, sum(cvp.vipScore) FROM ChainStoreSalesOrder cvp WHERE cvp.status=2 AND  cvp.orderDate BETWEEN ? AND ? AND cvp.vipCard.id IN "
					+ vipIdSet + " GROUP BY cvp.vipCard.id";
			Object[] sqlValue3 = new Object[] {startDate, endDate };
			List<Object> exChangeList = (List<Object>) this.executeHQLSelect(exChange, sqlValue3,
					null, false);
			for (Object object : exChangeList) {
				Object[] sales4 = (Object[]) object;
				vipExchangeMap.put(Common_util.getInt(sales4[0]), Common_util.getDouble(sales4[1]));
			}
			
			return vipExchangeMap;
		}
		
		/**
		 * VIP 当前积分
		 * @param vipIdSet
		 * @param startDate
		 * @param endDate
		 * @return
		 */
		public Map<Integer, Double> getNowIntegralMap(String vipIdSet,java.util.Date startDate,java.util.Date endDate) {
			Map<Integer, Double> nowIntegralMap = new HashMap<Integer, Double>();
			String nowIntegralSql = "SELECT cvp.vipCardId, sum(cvp.coupon) FROM ChainVIPScore cvp WHERE  cvp.vipCardId IN " + vipIdSet + " GROUP BY cvp.vipCardId";
			List<Object> nowIntegralList = (List<Object>) this.executeHQLSelect(nowIntegralSql, null,null, false);
			for (Object object : nowIntegralList) {
				Object[] sales5 = (Object[]) object;
				nowIntegralMap.put(Common_util.getInt(sales5[0]), Common_util.getDouble(sales5[1]));
			}
			return nowIntegralMap;
		}
		
		
		/**
		 * //最后消费日期
		 * @param vipIdSet
		 * @param startDate
		 * @param endDate
		 * @return
		 */
		public Map<Integer, String> getEndDataMap(String vipIdSet,java.util.Date startDate,java.util.Date endDate) {
			Map<Integer, String> endDataMap = new HashMap<Integer, String>();
			String endDataSql = "SELECT cvp.vipCardId, max(cvp.date) FROM ChainVIPScore cvp WHERE  cvp.vipCardId IN " + vipIdSet + " GROUP BY cvp.vipCardId";
			List<Object> endDataList = (List<Object>) this.executeHQLSelect(endDataSql, null,null, false);
			for (Object object : endDataList) {
				Object[] sales7 = (Object[]) object;
				endDataMap.put(Common_util.getInt(sales7[0]), Common_util.getString(sales7[1]));
			}
			return endDataMap;
		}

		
		
}
